from openpyxl import load_workbook
import pandas as pd
from utils import add_data


def add_kemu_to_tb(zt_file, tb_file, save_file,carry_forward_profit_account="本年利润"):
    '''
    将科目余额表中的数据复制到TB文件中
    :param zt_file:账套文件
    :param tb_file:tb文件
    :param save_file:保存后的tb文件
    :param carry_forward_profit_account:结转损益科目名称，默认为"本年利润"
    :return:
    '''
    df_kemu = pd.read_excel(zt_file, sheet_name='科目汇总表',dtype={'科目编号':str})
    # 选择科目编号长度为4位的一级科目
    df_kemu = df_kemu[df_kemu['科目编号'].apply(lambda x: len(str(x)) == 4)]
    # 获取利润表
    df_profit = check_profit_account(zt_file,carry_forward_profit_account)
    # 使用利润表替换科目余额表中的利润表项目
    df_kemu_new = pd.merge(df_kemu, df_profit, left_on='科目名称', right_on='一级科目名称', how='left')
    # 如果amount不等于0，则借方余额等于amount，贷方余额等于amount，否则借方余额和贷方余额等于原来
    df_kemu_new['借方本币金额'] = df_kemu_new.apply(lambda x: x['amount'] if (abs(x['amount']) > 0.00) else x["借方本币金额"], axis=1)
    df_kemu_new['贷方本币金额'] = df_kemu_new.apply(lambda x: x['amount'] if (abs(x['amount']) > 0.00) else x["贷方本币金额"], axis=1)
    # 获取科目余额表的数据
    kemu_code = df_kemu_new['科目编号'].tolist()
    kemu_name = df_kemu_new['科目名称'].tolist()
    start = df_kemu_new['期初本币余额'].tolist()
    debit = df_kemu_new['借方本币金额'].tolist()
    credit = df_kemu_new['贷方本币金额'].tolist()
    end = df_kemu_new['期末本币余额'].tolist()
    # 获取科目余额表
    wb = load_workbook(tb_file)
    ws = wb['科目余额表']
    # 向科目余额表中添加数据
    add_data(ws,kemu_code,"D2","column")
    add_data(ws,kemu_name,"E2","column")
    add_data(ws,start,"G2","column")
    add_data(ws,debit,"H2","column")
    add_data(ws,credit,"I2","column")
    add_data(ws,debit,"J2","column")
    add_data(ws,credit,"K2","column")
    add_data(ws,end,"M2","column")

    wb.save(save_file)
    wb.close()


# 检查科目余额表中的结转损益科目是否正确
# 结转损益科目：
def check_profit_account(zt_file,carry_forward_profit_account="本年利润"):
    '''
    检查账套文件中结转损益科目是否正确
    :param zt_file: 账套文件
    :param carry_forward_profit_account: 损益结转科目名称
    :return: 损益类科目发生额
    '''
    # 获取账套文件中的科目表
    df_kemu = pd.read_excel(zt_file, sheet_name='会计科目', dtype={'科目编号': str,"科目级次":str})
    # 获取一级科目
    df_kemu_first = df_kemu[df_kemu['科目级次'] == '1']
    # 选择结转损益科目
    # 获取账套文件中的记账凭证表
    df_xsz = pd.read_excel(zt_file, sheet_name='记账凭证表', dtype={'科目编号': str, '一级科目编号': str})
#    选择损益结转凭证
    df_xsz_carry_forward = df_xsz[df_xsz['一级科目名称'].str.contains(carry_forward_profit_account)]
#    根据损益结转凭证的记账凭证编号，找出序时账中所有的分录行
    df_xsz_carry_forward_detail = df_xsz[df_xsz['记账凭证编号'].isin(df_xsz_carry_forward['记账凭证编号'])]
#     按照一级科目名称分类汇总上面分录对应的借方本币金额和贷方本币金额
    df_xsz_carry_forward_detail_group = df_xsz_carry_forward_detail.groupby(['一级科目名称']).agg({'借方本币金额':'sum','贷方本币金额':'sum'})
#     reset_index()将一级科目名称作为列名
    df_xsz_carry_forward_detail_group = df_xsz_carry_forward_detail_group.reset_index()
    # 检查损益结转的借贷方向
    df_profit_account = pd.merge(df_xsz_carry_forward_detail_group,df_kemu_first, left_on='一级科目名称', right_on='科目名称', how='left')
    # 如果借贷方向为借，则amount等于借方本币金额减去贷方本币金额，如果借贷方向为贷，则amount等于贷方本币金额减去借方本币金额
    df_profit_account['amount'] = df_profit_account.apply(lambda x: x['贷方本币金额'] - x['借方本币金额'] if x['余额方向'] == '借' else x['借方本币金额'] - x['贷方本币金额'], axis=1)
    df_profit_account = df_profit_account[['一级科目名称','amount']]
    return df_profit_account





if __name__ == '__main__':
    # 账套文件
    zt_file = r"D:\我的文件2026\义乌城投2023年度审计\公司账套\义乌市城投供应链管理有限公司 - 副本.xlsx"
    # tb文件
    tb_file = r"D:\audit\audit_report\project\model-义乌城投\model.xlsx"
    # 保存副本文件名
    save_file = r'D:\audit\audit_report\project\model-义乌城投\model - 副本.xlsx'
    # 结转损益科目名称
    carry_forward_profit_account = "本年利润"
    add_kemu_to_tb(zt_file, tb_file, save_file, carry_forward_profit_account)